# coding: utf-8
"""
added by chenhj at 2018-03-12

若重启了之后, 服务没有正常运行, 则是因为celery_once的原因
解决方法:
    到celery_result所在的redis通道中:
    keys qo* (如果有值, 则del掉, 再重启就可以正常运行了)

"""

import datetime
import time
import json

from sqlalchemy.sql import text
from celery_once import QueueOnce

from common.schedule.seven_moor_celery import celery_app
from common.utils import redis_client
from common.seven_moor import Agent, WebCall
from common.constant import (
    TEL_X_AREA_MAP, OUTBOUND_USE_VIRTUAL_NUMBER, OUTBOUND_KEY
)
from common.models import model_factory
from common.utils import DB


@celery_app.task(
    name='seven_moor_outbound_task',
    base=QueueOnce,
    once={'unlock_before_run': True, 'graceful': True}
)
def seven_moor_outbound_task():
    """
    add by zhouhl 2018-01-11
    七陌外呼定时任务，执行间隔10秒，程序内判断只在8点至24点之间执行
    执行流程：
        1. 调用七陌接口，查询在线且空闲的座席数目
        2. 取当前处理中的任务数量
        3. 按照计划时间查询外呼任务并调用七陌的webcall进行外呼
    """
    current_hour = datetime.datetime.now().hour
    if 8 <= current_hour < 22:
        with DB() as session:
            idle_agent_num, busy_agent_num = Agent.query_idle_agent_num()

            if idle_agent_num > 0:
                push_new_outbound(session, idle_agent_num, busy_agent_num)
    # NOTE by chenhj, 这里需要在if分支的外边, 否则晚上10点之后任务就自动停了
    now = datetime.datetime.utcnow()
    seven_moor_outbound_task.apply_async(
        eta=now + datetime.timedelta(seconds=2),
        expires=now + datetime.timedelta(seconds=5)
    )


def push_new_outbound(session, idle_agent_num, busy_agent_num):
    """
    add by zhouhl 2018-01-11
    根据在线客服与处理中的任务，推送新的外呼任务
    根据配置项，判断是否给外呼任务的号码绑定虚拟号
    """
    QueueStateWaiting = 1

    # schedule_time 比当前时间加1分钟，任务量少的时候尽量做到任务提前
    schedule_time = datetime.datetime.now() + datetime.timedelta(minutes=1)

    # 将5分钟前更新的，queue_state=2的outbound_record修改为queue_state=1
    # 重新推送，防止阻塞自动外呼 add by zhouhl 2018-03-02

    select_timeout_outbound_sql = text("""
        select
            virtual_number_id
        from
            outbound_records
        where
            queue_state = 2
            and updated_at < :updated_at;
    """)

    unbind_timeout_virt_num_sql = text("""
        update
            virtual_numbers
        set
            valid=0
        where
            id=:id
    """)

    update_timeout_outbound_sql = text("""
        update
            outbound_records
        set
            queue_state = 1,
            updated_at = UNIX_TIMESTAMP()
        where
            queue_state = 2
            and updated_at < :updated_at;
    """)

    # 查询当前处理的外呼任务数量
    select_handing_outbound_num_sql = text("""
        select
            count(*)
        from
            outbound_records
        where
            queue_state = 2;
    """)

    # 按照计划时间查询外呼任务
    select_waiting_outbound_sql = text("""
        select
            id,mobile,need_virtual_number
        from
            outbound_records
        where
            queue_state = :queue_state
            and schedule_time <= :schedule_time
        order by
            call_weight asc,
            schedule_time asc,
            created_at asc
        limit :idle_agent_num;
    """)

    # 更新 outbound_record 的 webcall_action_id
    update_outbound_webcall_action_id = text("""
        update
            outbound_records
        set
            webcall_action_id = :webcall_action_id,
            queue_state = 2,
            updated_at = UNIX_TIMESTAMP()
        where
            id = :id;
    """)

    timeout_updated_at = int(time.time()) - 60 * 5

    # 把超时的 outbound 解绑对应虚拟号, 更新queue_state状态
    timeout_agents = session.execute(
        select_timeout_outbound_sql,
        {
            'updated_at': timeout_updated_at
        }
    ).fetchall()
    for each_timeout_agent in timeout_agents:
        if each_timeout_agent.virtual_number_id:
            session.execute(
                unbind_timeout_virt_num_sql,
                {
                    'id': each_timeout_agent.virtual_number_id
                }
            )

    session.execute(
        update_timeout_outbound_sql,
        {
            'updated_at': timeout_updated_at
        }
    )

    handing_outbound_num = session.execute(
        select_handing_outbound_num_sql
    ).fetchone()[0]

    # 处理中的外呼数大于所有座席数时，直接返回
    if handing_outbound_num >= idle_agent_num + busy_agent_num:
        return

    query_params = dict(
        queue_state=QueueStateWaiting,
        schedule_time=schedule_time,
        idle_agent_num=idle_agent_num,
    )
    tmp_pending_handle_outbound_list = session.execute(
        select_waiting_outbound_sql,
        query_params
    ).fetchall()

    # 七陌的webcall居然支持呼叫虚拟号了 by zhouhl 2018-03-21
    outbound_use_virtual_number = json.loads(
        redis_client.get(OUTBOUND_USE_VIRTUAL_NUMBER) or 'true')

    # add by zhouhl 2018-04-02 缓存执行外呼 outbound.id，防止重复绑定虚拟号
    pending_handle_outbound_list = []
    for i in tmp_pending_handle_outbound_list:
        outbound_key = OUTBOUND_KEY.format(outbound_id=i.id)
        if not redis_client.get(outbound_key):
            pending_handle_outbound_list.append(i)
            redis_client.set(
                outbound_key, 1, ex=60 * 5
            )

    pending_handle_outbounds = [
        {
            'id': i.id,
            'mobile': i.mobile,
            'need_virtual_number': i.need_virtual_number,
            'virtual_num_info': bind_virtual_number(session, i) \
                if outbound_use_virtual_number else '',
        }
        for i in pending_handle_outbound_list
    ]

    if pending_handle_outbounds:
        for outbound in pending_handle_outbounds:
            virtual_number_id = None
            dialout_num = outbound['mobile']
            if outbound['virtual_num_info']:
                dialout_num = outbound['virtual_num_info'][0]
                virtual_number_id = outbound['virtual_num_info'][1]

            # 调用七陌webcall接口
            response = WebCall.make_web_call(
                Exten=dialout_num
            )
            print('*' * 50)
            print('make web call')
            print('dialout_num:', dialout_num)
            print('real_num:', outbound['mobile'])
            print(response)
            print('*' * 50)

            # 判断调用接口是否成功的判断
            if response.get('Succeed'):
                # 更新 outbound 信息
                action_id = response.get('ActionID')
                if action_id:
                    session.execute(
                        update_outbound_webcall_action_id,
                        {
                            'webcall_action_id': action_id,
                            'id': outbound['id'],
                        }
                    )
            else:
                if virtual_number_id:
                    session.execute(
                        unbind_timeout_virt_num_sql,
                        {
                            'id': virtual_number_id
                        }
                    )


def bind_virtual_number(session, outbound_record):
    """
    add by zhouhl 2018-01-12
    根据外呼任务的配置，绑定虚拟号并更新数据库
    1. 先查询该外呼任务的号码是否存在axn关系，需要防止把这些x号再绑定，防止与其>他业务冲突
    2. 查找可用的虚拟号，并绑定axn关系
    3. 更新外呼记录相关信息
    """
    if not outbound_record.need_virtual_number:
        return ''

    mobile = outbound_record.mobile

    # 先查询是否已经有有效的axn虚拟号关系
    find_existed_valid_sql = text("""
        select
            id, tel_x
        from
            virtual_numbers
        where
            tel_a=:tel_a and tel_b='' and valid=1 and for_sms=false;
    """)

    # 根据mobile查user信息，获取省份信息
    find_user_sql = text("""
        select
            id, area_id
        from
            user
        where
            mobile = :mobile
            and deleted_at=0;
    """)

    # 查询是否还有其他axn关系，这些x号不能再使用
    find_used_telx_sql = text("""
        select
            distinct tel_x
        from
            virtual_numbers
        where
            tel_b=''
            and valid=1;
    """)

    # 查找可用的虚拟号
    find_usable_telx_sql = """
        select
            tel
        from
            number_pool_for_lt
        where
            deleted_at=0
            and reserved=false
            {used_tel_x_condition}
            {area_condition}
        order by
            use_count asc,
            updated_at asc;
    """

    add_virtual_number_use_count_sql = text("""
        update
            number_pool_for_lt
        set
            use_count=use_count+1,
            updated_at=UNIX_TIMESTAMP()
        where
            tel=:tel;
    """)

    # 写入axn关系
    insert_virtual_number_sql = text("""
        insert into virtual_numbers(
            tel_a, tel_b, tel_x, created_at, updated_at
        )values(
            :tel_a, '', :tel_x, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
        );
    """)

    find_last_virtual_number_id_sql = text("""
        select LAST_INSERT_ID();
    """)

    # 更新outbound信息
    update_outbound_record_sql = text("""
        update
            outbound_records
        set
            virtual_number_id=:virtual_number_id
        where
            id=:outbound_record_id;
    """)

    # 先查询是否已有有效绑定记录，有则直接返回
    existed_data = session.execute(
        find_existed_valid_sql,
        {
            'tel_a': mobile
        }
    ).fetchone()
    if existed_data:
        return existed_data.tel_x, existed_data.id

    # 查找该mobile对应的user，获取地区信息
    user_info = session.execute(
        find_user_sql,
        dict(
            mobile=mobile,
        )
    ).fetchone()
    if user_info:
        prov_id = str(user_info.area_id)[:2] + '0000'
    else:
        # 没有对应账户，默认使用广东的虚拟号
        prov_id = '440000'
    area_ids = TEL_X_AREA_MAP.get(prov_id)

    # 查找不可使用的虚拟号
    used_telx_list = session.execute(
        find_used_telx_sql
    ).fetchall()
    used_telx = tuple([i.tel_x for i in used_telx_list])

    area_condition = 'and area_id in :area_ids' if area_ids else ''
    used_tel_x_condition = 'and tel not in :used_telx' if used_telx else ''

    area_usable_tel_x_sql = text(
        find_usable_telx_sql.format(
            area_condition=area_condition,
            used_tel_x_condition=used_tel_x_condition
        )
    )

    usable_telx = session.execute(
        area_usable_tel_x_sql,
        dict(used_telx=used_telx, area_ids=area_ids)
    ).fetchone()

    if usable_telx:
        session.execute(
            insert_virtual_number_sql,
            dict(tel_a=mobile, tel_x=usable_telx.tel)
        )

        last_virtual_number_id = session.execute(
            find_last_virtual_number_id_sql).fetchone()[0]

        session.execute(
            update_outbound_record_sql,
            dict(
                virtual_number_id=last_virtual_number_id,
                outbound_record_id=outbound_record.id
            )
        )

        session.execute(
            add_virtual_number_use_count_sql,
            dict(tel=usable_telx.tel)
        )
        return usable_telx.tel, last_virtual_number_id
    else:
        return ''


@celery_app.task(name='seven_moor_get_audio_file')
def seven_moor_get_audio_file_task(
        record_url, audio_file_name, mobile,
        record_datetime, call_id, date_info
):
    """
    created by chenhj at 29/01/2018
    atler by zhouhl 20180428
    """
    import os
    import sys
    base_path = os.path.dirname(os.path.abspath(__file__))
    sys.path.append(os.path.join(base_path, '../..'))

    import env_config
    import datetime
    import requests

    try_time = 0
    max_time = 5

    while try_time < max_time:
        tmp_audio_response = requests.get(record_url)
        if tmp_audio_response.status_code == 200:
            # 保存文件
            media_path = os.environ.get('MEDIA_PATH') \
                or '/var/www/prod/wph_opc/pubilc/storage/audio/ccx'
            save_file_dir = os.path.join(media_path, date_info)
            if not os.path.exists(save_file_dir):
                os.makedirs(save_file_dir)

            save_file_path = os.path.join(save_file_dir, audio_file_name)
            with open(save_file_path, 'wb') as f:
                f.write(tmp_audio_response.content)

            print('*' * 50)
            print(save_file_path)
            print('*' * 50)

            # 直接将相关记录写入数据库，不再访问api接口了
            with DB() as session:
                tmp_audio_path = '/'.join(save_file_path.split('/')[-3:])
                tmp_record_date = datetime.date(
                    *time.strptime(date_info, "%Y%m%d")[0:3])
                audio_record = model_factory.audio.create(
                    session,
                    mobile=mobile,
                    audio=tmp_audio_path,
                    record_date=tmp_record_date,
                    record_datetime=record_datetime,
                    seven_moor_call_id=call_id,
                )
                # 如果存在该 seven_moor_call_id 的 Demand， 则关联录音
                demand = model_factory.demand.get_by_args(
                    session,
                    seven_moor_call_id=call_id
                )
                if demand:
                    demand.audio = audio_record.audio

            break
        # 小概率会存在七陌录音还没同步，此时等待3秒，会引起阻塞
        time.sleep(3)
        try_time += 1


if __name__ == '__main__':
    #seven_moor_outbound_task()

    #print(Agent.dialout('8000', '13174810407'))

    #print(WebCall.bridge_web_call(
    #    Exten='18817877341',
    #    Variable='13174810407',
    #))

    #print(Agent.query_idle_agent_num())
    Agent.list_agent_state()

    # print(WebCall.make_web_call(
    #     #Exten='13421376954'
    #     Exten='13162403524'
    # ))

    pass
